"""
用户认证和权限管理数据模型
遵循DataPlexus现有的数据库设计规范
"""

from sqlalchemy import Column, BigInteger, String, Boolean, DateTime, Text, JSON, ForeignKey, Integer, Enum as SQLEnum, UniqueConstraint, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime
from enum import Enum
from typing import List
from app.core.database import Base
from app.utils.timezone_utils import get_shanghai_now





class User(Base):
    """用户模型"""
    __tablename__ = "dp_users"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='用户ID')
    username = Column(String(50), unique=True, nullable=False, comment='用户名')
    email = Column(String(100), unique=True, nullable=False, comment='邮箱地址')
    password_hash = Column(String(255), nullable=False, comment='密码哈希值')
    full_name = Column(String(100), comment='真实姓名')
    avatar_url = Column(String(255), comment='头像URL')
    phone = Column(String(20), comment='手机号码')
    department = Column(String(100), comment='部门')
    position = Column(String(100), comment='职位')
    is_active = Column(Boolean, default=True, comment='是否激活')
    is_superuser = Column(Boolean, default=False, comment='是否超级用户')
    last_login = Column(DateTime, comment='最后登录时间')
    login_attempts = Column(Integer, default=0, comment='登录尝试次数')
    locked_until = Column(DateTime, comment='锁定到期时间')
    password_changed_at = Column(DateTime, comment='密码修改时间')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')
    updated_at = Column(DateTime, default=get_shanghai_now, onupdate=get_shanghai_now, comment='更新时间')

    # 关联关系
    user_roles = relationship("UserRole", back_populates="user", cascade="all, delete-orphan", foreign_keys="UserRole.user_id")
    sessions = relationship("UserSession", back_populates="user", cascade="all, delete-orphan")
    assigned_roles = relationship("UserRole", foreign_keys="UserRole.assigned_by")

    def __repr__(self):
        return f"<User(id={self.id}, username='{self.username}')>"

    def get_active_roles(self):
        """获取用户的所有激活角色（避免在property中进行数据库查询）"""
        return [ur.role for ur in self.user_roles if ur.is_active and ur.role and ur.role.is_active]

    def get_permissions(self):
        """获取用户的所有权限（避免在property中进行数据库查询）"""
        permissions = set()
        for ur in self.user_roles:
            # 更宽松的布尔值检查，处理MySQL tinyint(1)的情况
            ur_active = ur.is_active in (True, 1, '1')
            role_active = ur.role and (ur.role.is_active in (True, 1, '1', None))  # None也视为激活

            if ur_active and ur.role and role_active:
                for rp in ur.role.role_permissions:
                    rp_active = rp.is_active in (True, 1, '1')
                    if rp_active and rp.permission:
                        permissions.add(rp.permission)
        return list(permissions)

    def has_permission(self, permission_name: str) -> bool:
        """检查用户是否拥有指定权限"""
        if self.is_superuser:
            return True
        return any(p.name == permission_name for p in self.get_permissions())

    def has_role(self, role_name: str) -> bool:
        """检查用户是否拥有指定角色"""
        return any(r.name == role_name for r in self.get_active_roles())


class Role(Base):
    """角色模型"""
    __tablename__ = "dp_roles"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='角色ID')
    name = Column(String(50), unique=True, nullable=False, comment='角色名称')
    display_name = Column(String(100), nullable=False, comment='角色显示名称')
    description = Column(Text, comment='角色描述')
    is_system = Column(Boolean, default=False, comment='是否系统角色')
    is_active = Column(Boolean, default=True, comment='是否激活')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')
    updated_at = Column(DateTime, default=get_shanghai_now, onupdate=get_shanghai_now, comment='更新时间')

    # 关联关系
    user_roles = relationship("UserRole", back_populates="role")
    role_permissions = relationship("RolePermission", back_populates="role", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Role(id={self.id}, name='{self.name}')>"

    @property
    def permissions(self):
        """获取角色的所有权限"""
        return [rp.permission for rp in self.role_permissions if rp.is_active and rp.permission]


class Permission(Base):
    """权限模型"""
    __tablename__ = "dp_permissions"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='权限ID')
    name = Column(String(100), unique=True, nullable=False, comment='权限名称')
    display_name = Column(String(100), nullable=False, comment='权限显示名称')
    description = Column(Text, comment='权限描述')
    resource = Column(String(50), nullable=False, comment='资源类型')
    action = Column(String(50), nullable=False, comment='操作类型')
    is_system = Column(Boolean, default=False, comment='是否系统权限')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')
    updated_at = Column(DateTime, default=get_shanghai_now, onupdate=get_shanghai_now, comment='更新时间')

    # 关联关系
    role_permissions = relationship("RolePermission", back_populates="permission")
    route_permissions = relationship("RoutePermission", back_populates="permission")

    def __repr__(self):
        return f"<Permission(id={self.id}, name='{self.name}')>"

    def get_routes(self) -> List['Route']:
        """获取需要此权限的路由列表"""
        return [rp.route for rp in self.route_permissions if rp.is_active]

    def get_child_permissions(self) -> List['Permission']:
        """获取子权限列表"""
        return [ph.child_permission for ph in self.child_hierarchies]

    def get_parent_permissions(self) -> List['Permission']:
        """获取父权限列表"""
        return [ph.parent_permission for ph in self.parent_hierarchies]


class UserRole(Base):
    """用户角色关联模型"""
    __tablename__ = "dp_user_roles"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='关联ID')
    user_id = Column(BigInteger, ForeignKey('dp_users.id', ondelete='CASCADE'), nullable=False, comment='用户ID')
    role_id = Column(BigInteger, ForeignKey('dp_roles.id', ondelete='CASCADE'), nullable=False, comment='角色ID')
    assigned_by = Column(BigInteger, ForeignKey('dp_users.id', ondelete='SET NULL'), comment='分配者ID')
    assigned_at = Column(DateTime, default=get_shanghai_now, comment='分配时间')
    expires_at = Column(DateTime, comment='过期时间')
    is_active = Column(Boolean, default=True, comment='是否激活')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')

    # 关联关系
    user = relationship("User", back_populates="user_roles", foreign_keys=[user_id])
    role = relationship("Role", back_populates="user_roles")
    assigner = relationship("User", foreign_keys=[assigned_by])

    def __repr__(self):
        return f"<UserRole(user_id={self.user_id}, role_id={self.role_id})>"


class RolePermission(Base):
    """角色权限关联模型"""
    __tablename__ = "dp_role_permissions"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='关联ID')
    role_id = Column(BigInteger, ForeignKey('dp_roles.id', ondelete='CASCADE'), nullable=False, comment='角色ID')
    permission_id = Column(BigInteger, ForeignKey('dp_permissions.id', ondelete='CASCADE'), nullable=False, comment='权限ID')
    granted_by = Column(BigInteger, ForeignKey('dp_users.id', ondelete='SET NULL'), comment='授权者ID')
    granted_at = Column(DateTime, default=get_shanghai_now, comment='授权时间')
    is_active = Column(Boolean, default=True, comment='是否激活')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')

    # 关联关系
    role = relationship("Role", back_populates="role_permissions")
    permission = relationship("Permission", back_populates="role_permissions")
    granter = relationship("User", foreign_keys=[granted_by])

    def __repr__(self):
        return f"<RolePermission(role_id={self.role_id}, permission_id={self.permission_id})>"


class UserSession(Base):
    """用户会话模型"""
    __tablename__ = "dp_user_sessions"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='会话ID')
    user_id = Column(BigInteger, ForeignKey('dp_users.id', ondelete='CASCADE'), nullable=False, comment='用户ID')
    session_token = Column(String(255), unique=True, nullable=False, comment='会话令牌')
    refresh_token = Column(String(255), comment='刷新令牌')
    device_info = Column(JSON, comment='设备信息')
    ip_address = Column(String(45), comment='IP地址')
    user_agent = Column(Text, comment='用户代理')
    is_active = Column(Boolean, default=True, comment='是否激活')
    expires_at = Column(DateTime, nullable=False, comment='过期时间')
    last_activity = Column(DateTime, default=get_shanghai_now, comment='最后活动时间')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')

    # 关联关系
    user = relationship("User", back_populates="sessions")

    def __repr__(self):
        return f"<UserSession(id={self.id}, user_id={self.user_id})>"

    @property
    def is_expired(self) -> bool:
        """检查会话是否过期"""
        return get_shanghai_now() > self.expires_at





class Route(Base):
    """路由模型 - 存储系统所有路由信息"""
    __tablename__ = "dp_routes"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='路由ID')
    path = Column(String(255), unique=True, nullable=False, comment='路由路径')
    name = Column(String(100), nullable=False, comment='路由名称')
    display_name = Column(String(100), nullable=False, comment='显示名称')
    description = Column(Text, comment='路由描述')
    icon = Column(String(50), comment='图标名称')
    component = Column(String(255), comment='组件路径')
    parent_id = Column(BigInteger, ForeignKey('dp_routes.id'), comment='父路由ID')
    sort_order = Column(Integer, default=0, comment='排序顺序')
    is_menu = Column(Boolean, default=True, comment='是否显示在菜单中')
    is_active = Column(Boolean, default=True, comment='是否启用')
    is_system = Column(Boolean, default=False, comment='是否系统路由')
    meta = Column(JSON, comment='路由元数据')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')
    updated_at = Column(DateTime, default=get_shanghai_now, onupdate=get_shanghai_now, comment='更新时间')

    # 关联关系
    parent = relationship("Route", remote_side=[id], back_populates="children")
    children = relationship("Route", back_populates="parent", cascade="all, delete-orphan")
    route_permissions = relationship("RoutePermission", back_populates="route", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Route(id={self.id}, path='{self.path}', name='{self.name}')>"

    def get_permissions(self) -> List['Permission']:
        """获取路由所需的权限列表"""
        return [rp.permission for rp in self.route_permissions if rp.is_active]

    def get_permission_names(self) -> List[str]:
        """获取路由所需的权限名称列表"""
        return [permission.name for permission in self.get_permissions()]


class RoutePermission(Base):
    """路由权限关联模型 - 建立路由与权限的多对多关系"""
    __tablename__ = "dp_route_permissions"

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='关联ID')
    route_id = Column(BigInteger, ForeignKey('dp_routes.id'), nullable=False, comment='路由ID')
    permission_id = Column(BigInteger, ForeignKey('dp_permissions.id'), nullable=False, comment='权限ID')
    is_required = Column(Boolean, default=True, comment='是否必需权限')
    is_active = Column(Boolean, default=True, comment='是否启用')
    created_by = Column(BigInteger, ForeignKey('dp_users.id'), comment='创建者ID')
    created_at = Column(DateTime, default=get_shanghai_now, comment='创建时间')

    # 关联关系
    route = relationship("Route", back_populates="route_permissions")
    permission = relationship("Permission", back_populates="route_permissions")
    creator = relationship("User")

    def __repr__(self):
        return f"<RoutePermission(id={self.id}, route_id={self.route_id}, permission_id={self.permission_id})>"



